package util;

import java.sql.*;

/**
 * Created by Tiler on 2020/10/28
 */
public class JDBCUtil {
    //final用在类上、方法上、变量上的区别?
    //static的作用?
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/bookstore?serverTimezone=UTC";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    //成员变量
    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet resultSet = null;

    //创建对象时自动与数据库建立连接
    public JDBCUtil() {
        getConnection();
    }

    private Connection getConnection() {
        try {
            //加载mysql驱动
            Class.forName(DRIVER);

            //获得一个数据库的连接
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    //可变参数：参数数量不一定
    public ResultSet excuteQuery(String sql, Object... params) {
        try {
            statement = connection.prepareStatement(sql);

            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }

            resultSet = statement.executeQuery();

            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();

            return null;
        }
    }
    //可变参数：参数数量不一定，returnId:是否返回自增主键
    public Integer excuteUpdate(String sql, Boolean returnId, Object... params) {
        try {
            if (returnId) {
                //执行sql语句并返回自增主键
                statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            }
            else {
                statement = connection.prepareStatement(sql);
            }

            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1, params[i]);
            }

            Integer status = statement.executeUpdate();

            //不返回自增主键
            if (!returnId) {
                return status;
            }

            //需要返回主键
            ResultSet rSet = statement.getGeneratedKeys();
            if (rSet.next()) {
                return rSet.getInt(1); //取第一列的值
            }
            else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

            return 0;
        }
    }

    //可变参数：参数数量不一定
    public int[] excuteBatch(String sql, Object[][] params) {
        try {
            statement = connection.prepareStatement(sql);

            //循环所有的输入参数
            for (int i = 0; i < params.length; i++) {
                //循环每一个参数，每一个参数为一个数组
                for(int j = 0; j < params[i].length; j++) {
                    statement.setObject(j+1, params[i][j]);
                }
                //加入批量处理
                statement.addBatch();
            }

            //执行批量处理
            return statement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();

            return null;
        }
    }

    public void close() {
        //关闭结果集
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            //关闭操作对象
            if (statement != null) {
                statement.close();
            }

            //关闭连接
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
